<?php
########################################################
# DMS Donations Management System v1.0                 #
# created by:  Isaac Sabas                             #
#			   Nikko Reyes							   #
#			   Miko Tiamwatt						   #
#                                                      #
# De La Salle Philippines                              #
# All Rights Reserved DLSP Copyright 2009              #
########################################################

# INFORMATION:
# TicketHandle.php 
# 	- this is the class that handles the connection of the call ticket window to the database
#	- all MODIFY, SELECT, and DELETE functions are housed in this class
# 	
# FUNCTION INFORMATION:
#	1. CONSTRUCTOR:
#		- sets up the database connection
#	2. LOAD INFORMATION DATA:
#		a. getTicket (ticketId) - returns all the data fields of a particular ticket
#		b. getAllClientTickets (clientId) - returns all the tickets and its data fields of a particular client
#		c. getClientFollowupList (clientId, status) - returns all the clients with a particular followup status (done or not)
#		d. getAgentClientToFollowup (agentId, status) - returns all the clients of a particular agent who are subjected for followup-based on the status (done or not)
#	3. STORE DATA:
#		a. addNewTicket (valuesArray, clientId) - creates a new transaction for a particular client
# 		b. addFollowUp (valuesArray, clientId) - add the client to the followup list
# 	4. MODIFY DATA:
#		a. completeFollowup (followupId) - changes the status of the followup as complete
# 

class TicketHandle{
# FOR CLASS MANAGEMENT #
# 	The following variables are set to uppercase. This means that they are    
#	class monitoring variables.									              

	# CLASS MESSAGE_HANDLE HOLDER #
	# handles the error and notification messages of the class to notify the user 
	# to capture all sql and system error, notifications and logs 
		public $MESSAGE_HANDLE = NULL;

	# DB_HANDLE INSTANCE HOLDER #
	# database connection handler [ MYSQL ]
	# instance of the database handle is placed here for the system to use the database with ease 
		public $DB_HANDLE = NULL;
		
# TICKET VARIABLES #
# the ticket variables represent the data fields of the database itself
	private $ticketArray;
	
# CLASS CONSTANT VARIABLES #
# this is for class management purposes
	private $errorArray = array("DB_NOTCONNECTED" => "Not connected to database.",
							    "DB_QUERYERROR" => "Problem with query",
							    "DB_INVALIDFIELD" => "Invalid field being accessed."
							   );
	# call status:
	# 0 = new
	# 1 = success
	# 2 = failed
	
	# call purpose:
	# 0 = new call
	# 1 = for follow up
	# 2 = for greeting
	# 3 = for pick up reminder
	# 4 = for post-pick up reminders
	
	# followup status:
	# 0 = not yet called
	# 1 = called/ was already followed up

###################################
# TICKET HANDLE CLASS CONSTRUCTOR #
###################################
	public function TicketHandle(){
		# include the needed / required library
			require_once('DBHandle.php');
		# instantiate the DBHandle and assign it to the class attribute DB_HANDLE	
			$databaseConnection = new DBHandle();
			$this->DB_HANDLE = $databaseConnection->DB_HANDLE;
	}	


##############################	
# LOAD SYSTEM DATA FUNCTIONS #	
##############################	

	public function getTicket ($ticketId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$ticketId = addslashes($ticketId);
				# prepare the statement
					$statement = "SELECT * FROM ticket_calls WHERE ticket_id = '$ticketId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[TicketHandle:getTicket]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
					
						# get the entry values
							$resultArray = mysql_fetch_assoc($result);
						# return the values											
							return $resultArray;
					}		
			//}
	}
	
	public function getAllClientTickets ($clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$clientId = addslashes($clientId);
				# prepare the statement
					$statement = "SELECT * FROM ticket_calls WHERE client_id = '$clientId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[TicketHandle:getAllClientTickets]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
					
						# get the entry values
							while ($row = mysql_fetch_assoc($result)){ 
								$resultArray[] = $row; 
							}
						# return the values											
							return $resultArray;
					}		
			//}
	}
	
	public function getClientFollowupList ($clientId, $status) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$clientId = addslashes($clientId);
					$status = addslashes($status);
				# prepare the statement
					$statement = "SELECT * FROM followups WHERE client_id = '$clientId' AND followup_status = '$status'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[TicketHandle:getClientFollowupList]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
					
						# get the entry values
							while ($row = mysql_fetch_assoc($result)){ 
								$resultArray[] = $row; 
							}
						# return the values											
							return $resultArray;
					}		
			//}
	}
	
	public function getAgentClientToFollowup ($agentId, $status) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$agentId = addslashes($agentId);
					$status = addslashes($status);
				# prepare the statement
					$statement = "SELECT * FROM followups WHERE agent_id = '$agentId' AND followup_status = $status";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[TicketHandle:getAgentClientToFollowup]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
					
						# get the entry values
							while ($row = mysql_fetch_assoc($result)){ 
								$resultArray[] = $row; 
							}
						# return the values											
							return $resultArray;
					}		
			//}
	}

###############################	
# STORE SYSTEM DATA FUNCTIONS #	
###############################	

	# the store functions stores the values to the class' attributes and then connects to the database to store the value
	# it is already assumed that the script that will provide the necessary input validation and error checking
	# the only security the setters provide is the addslashes() function to protect SQL injection from happening
	
		public function addNewTicket ($valuesArray, $agentId, $clientId) {
			# check if the DB_HANDLE has connected to the database
				/*if ($this->DB_HANDLE == NULL) {
					return $errorArray['DB_NOTCONNECTED'];
				}
				else {	*/	
					$clientId = addslashes($clientId);
					$agentId = addslashes($agentId);
					# prepare the statement 
						$statement = "INSERT INTO ticket_calls (				
								agent_id,
								client_id,
								ticket_date,
								ticket_time,
								ticket_callstatus,
								ticket_callpurpose,
								ticket_donationdenied,
								ticket_followupdate,
								ticket_followuptime,
								ticket_notes
							) 
							VALUES (
								'{$agentId}',
								'{$clientId}',
								'{$valuesArray['ticket_date']}',
								'{$valuesArray['ticket_time']}',
								'{$valuesArray['ticket_callstatus']}',
								'{$valuesArray['ticket_callpurpose']}',
								'{$valuesArray['ticket_donationdenied']}',
								'{$valuesArray['ticket_followupdate']}',
								'{$valuesArray['ticket_followuptime']}',
								'{$valuesArray['ticket_notes']}'
							)";
						
					# query & check if error has occured
						if(!$query = mysql_query($statement)) {
							return $errorArray['DB_QUERYERROR'] . ' -[TicketHandle:addNewTicket]';
						}
						else {
							# debugging purposes <remove on production>	
								//echo "updated the database";
				
							# return the values											
								return mysql_insert_id();
						}
				//}
		}
		
		public function addFollowUp ($valuesArray, $ticketId, $agentId, $clientId) {
			# check if the DB_HANDLE has connected to the database
				/*if ($this->DB_HANDLE == NULL) {
					return $errorArray['DB_NOTCONNECTED'];
				}
				else {	*/	
					# prepare the statement 
						$statement = "INSERT INTO call_list (				
								agent_id,
								client_id,
								call_tocalldate,
								call_tocalltime,
								call_purpose,
								call_priority,
								call_status,
								ticket_id
							) 
							VALUES (
								'{$agentId}',
								'{$clientId}',
								'{$valuesArray['call_tocalldate']}',
								'{$valuesArray['call_tocalltime']}',
								'1',
								'For Follow-up',
								'0',
								'{$ticketId}'
							)";
						
					# query & check if error has occured
						if(!$result = mysql_query($statement)) {
							return $errorArray['DB_QUERYERROR'] . ' -[TicketHandle:addFollowup]';
						}
						else {
							# debugging purposes <remove on production>	
								//echo "updated the database";
				
							# return the values											
								return mysql_insert_id();
						}
				//}
		}
		
#########################
# MODIFY DATA FUNCTIONS #
#########################
	
	public function completeFollowup ($followupId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {	*/	
				$followupId = addslashes($followupId);
				# prepare the statement
					$statement = "UPDATE followups SET followup_status = 1 WHERE followup_id = '" . $followupId . "'";
				# query & check if error has occured
					if(!$query = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[TicketHandle:completeFollowup]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
			
						# return the values											
							return true;
					}
			//}		
	}
}	
?>
